package com.ysd.common.sqlbuilder;

import com.ysd.common.DataBaseType;
import com.ysd.common.GetApplicationContext;
import com.ysd.common.dto.ColumnValueDto;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;

/**
 * sql构建类
 *
 * @program: server_lis
 * @author: liuQi
 * @create: 2023-11-16 08:59
 */
@Slf4j
public class TransmitSqlBuilder {

    public String getDataBaseType() {
        DataBaseType bean = GetApplicationContext.applicationContext.getBean(DataBaseType.class);
        return bean.getDataBaseName();
    }

    public String buildFindTableNamesSql(@Param("tableName") String tableName) {
        String sql = "";
        if ("sqlserver".equals(getDataBaseType())) {
            sql = "SELECT T.NAME AS TABLE_NAME, P.VALUE AS COMMENTS\n" + "FROM SYS.TABLES AS T\n" + "LEFT JOIN SYS.EXTENDED_PROPERTIES AS P\n" + "    ON T.OBJECT_ID = P.MAJOR_ID\n" + "\t\tWHERE 1=1 \n" + "        AND P.MINOR_ID = 0\n" + "        AND P.NAME = 'MS_DESCRIPTION'\n" + "\t\t\t\tAND T.NAME LIKE '" + tableName + "%'";
        } else if ("oracle".equals(getDataBaseType()) || "dm".equals(getDataBaseType())) {
            sql = "SELECT TABLE_NAME, COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_NAME LIKE CONCAT('" + tableName + "', '%')";
        }
        return sql;
    }

    public String buildSelectColumnsByTableName(@Param("tableName") String tableName) {
        String sql = "";
        if ("sqlserver".equals(getDataBaseType())) {
            sql = "SELECT\n" + "B.column_id,\n" + "B.name as column_name,\n" + "C.value AS comments,\n" + "\n" + "d.data_type\n" + "FROM sys.tables A\n" + "INNER JOIN sys.columns B ON B.object_id = A.object_id\n" + "LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id\n" + "left join INFORMATION_SCHEMA.COLUMNS d on d.table_name = a.name and d.column_name = b.name\n" + "WHERE A.name = '" + tableName + "'\n";
        } else if ("oracle".equals(getDataBaseType()) || "dm".equals(getDataBaseType())) {
            sql = "select b.column_id, a.column_name, a.comments, b.data_type from user_col_comments a left join user_tab_columns b on a.column_name = b.column_name where a.table_name =CONCAT('" + tableName + "', '') and b.table_name = CONCAT('" + tableName + "', '') order by column_id ASC";
        }
        return sql;
    }

    public String buildInsertTransmitTodoSql(@Param("tableName") String tableName, @Param("list") List<ColumnValueDto> list) {
        return new SQL() {
            {
                INSERT_INTO(tableName);
                list.forEach(item -> {
                    if (item.getColumnType().equals("DATE") && ("oracle".equals(getDataBaseType()) || "dm".equals(getDataBaseType()))) {
                        VALUES(item.getColumn(), "to_date('" + item.getValue() + "', 'YYYY-MM-DD HH24:MI:SS')");
                    } else {
                        VALUES(item.getColumn(), "'" + item.getValue() + "'");

                    }
                });
            }
        }.toString();
    }

    public String buildUpdateTransmitTodoSql(@Param("tableName") String tableName, @Param("list") List<ColumnValueDto> list) {
        ColumnValueDto columnValueDto = list.stream().filter(item -> item.getColumn().equals("ID")).findFirst().get();
        return new SQL() {
            {
                UPDATE(tableName);
                list.stream().filter(item -> !item.getColumn().equals("ID")).forEach(item -> {
                    if (item.getColumnType().equals("DATE") && "oracle".equals(getDataBaseType())) {
                        SET(item.getColumn() + " = to_date('" + item.getValue() + "', 'YYYY-MM-DD HH24:MI:SS')");
                    } else {
                        SET(item.getColumn() + " = '" + item.getValue() + "'");
                    }
                });
                WHERE("ID = " + columnValueDto.getValue());
            }
        }.toString();
    }

    public static String buildSelectTransmitTodoSql(@Param("tableName") String tableName, @Param("columnList") List<String> columnList) {
        return new SQL() {
            {
                SELECT(columnList.toArray(new String[columnList.size()]));
                FROM(tableName);
                WHERE("1=1 and DEL_FLAG = 0 ");
                ORDER_BY("EDIT_TIME DESC");
            }
        }.toString();
    }
    public static String buildSelectTransmitTodoSqlByBusiness(@Param("tableName") String tableName,
                                                              @Param("outField") String outField,
                                                              @Param("indexCode") String indexCode,
                                                              @Param("itemCode") String itemCode,
                                                              @Param("majorCode") String majorCode,
                                                              @Param("moduleType") String moduleType,
                                                              @Param("columnList") List<String> columnList,
                                                              @Param("keywords")String keywords) {
        return new SQL() {
            {
                SELECT(columnList.toArray(new String[columnList.size()]));
                FROM(tableName);
                String sql="";
                if (StringUtils.isBlank(keywords)){
                    sql="1=1 and DEL_FLAG = 0 and not exists ("+ "select 1 from QI_INDEX_SQL_BUSINESS b where b.INDEX_CODE = '"+indexCode+"' and b.ITEM_CODE = '"+itemCode+"' and b.MAJOR_CODE = '"+majorCode+"' and b.MODULE_TYPE = '"+moduleType+"' and b.BUSINESS_VALUE ="+tableName+"."+outField+")";
                }else {
                    String likeSql="";
                    for (String column : columnList) {
                        likeSql+="or "+tableName+"."+column+" like '%"+keywords+"%'";
                    }
                    sql="1=1 and ("+likeSql.substring(3)+") and DEL_FLAG = 0 and not exists ("+ "select 1 from QI_INDEX_SQL_BUSINESS b where b.INDEX_CODE = '"+indexCode+"' and b.ITEM_CODE = '"+itemCode+"' and b.MAJOR_CODE = '"+majorCode+"' and b.MODULE_TYPE = '"+moduleType+"' and b.BUSINESS_VALUE ="+tableName+"."+outField+")";

                }
                WHERE(sql);
                ORDER_BY("EDIT_TIME DESC");
            }
        }.toString();
    }
    public static String buildSelectTransmitTodoSqlByMajor(@Param("tableName") String tableName,
                                                              @Param("outField") String outField,
                                                              @Param("moduleType") String moduleType,
                                                              @Param("columnList") List<String> columnList,
                                                              @Param("keywords")String keywords) {
        return new SQL() {
            {
                SELECT(columnList.toArray(new String[columnList.size()]));
                FROM(tableName);
                String sql="";
                if (StringUtils.isBlank(keywords)){
                    sql="1=1 and DEL_FLAG = 0 and not exists ("+ "select 1 from QI_MAJOR_BUSINESS b where "+" b.MODULE_TYPE = '"+moduleType+"' and b.BUSINESS_VALUE ="+tableName+"."+outField+")";
                }else {
                    String likeSql="";
                    for (String column : columnList) {
                        likeSql+="or "+tableName+"."+column+" like '%"+keywords+"%'";
                    }
                    sql="1=1 and ("+likeSql.substring(3)+") and DEL_FLAG = 0 and not exists ("+ "select 1 from QI_INDEX_SQL_BUSINESS b where "+" b.MODULE_TYPE = '"+moduleType+"' and b.BUSINESS_VALUE ="+tableName+"."+outField+")";

                }
                WHERE(sql);
                ORDER_BY("EDIT_TIME DESC");
            }
        }.toString();
    }

    public String buildSelectPageTodoSql(@Param("tableName") String tableName, @Param("columnList") List<String> columnList, @Param("startSet") Integer startSet, @Param("endSet") Integer endSet) {
        return new SQL() {
            {
                if ("sqlserver".equals(getDataBaseType())) {
                    SELECT("*");
                    FROM("(select " + String.join(",", columnList) + ", ROW_NUMBER () OVER ( ORDER BY EDIT_TIME DESC ) r FROM " + tableName + " a) temp");
                    WHERE("r between " + startSet + " and " + endSet);
                } else if ("oracle".equals(getDataBaseType()) || "dm".equals(getDataBaseType())) {
                    SELECT(String.join(",", columnList) + ",r");
                    FROM("(select " + String.join(",", columnList) + ", ROW_NUMBER () OVER ( ORDER BY EDIT_TIME DESC ) r FROM " + tableName + " a)");
                    WHERE("1 = 1 ");
                    AND();
                    WHERE("r between " + startSet + " and " + endSet);
                }
            }
        }.toString();
    }

    public String buildSelectCountSql(@Param("tableName") String tableName) {
        return new SQL() {{
            SELECT(" count(1) ");
            FROM(tableName);
            WHERE(" 1=1");
        }}.toString();
    }

    public String buildSelectCalcResultSql(@Param("sql") String sql) {
        String sqlStr = new SQL() {{
            SELECT("*");
            FROM(" (" + sql + ") T");
            WHERE(" 1=1 ");
        }}.toString();
        System.out.println(sqlStr);
        return sqlStr;
    }

    public String buildSelectDataBaseTables(@Param("dataBaseName") String dataBaseName) {
        String sqlStr = new SQL() {{
            SELECT(" owner, table_name ");
            FROM(" all_tables ");
            WHERE(String.format(" owner='%s'", dataBaseName));
        }}.toString();
        return sqlStr;
    }

}
